package com.sg.java.apps;

import com.google.common.collect.Lists;
import org.apache.commons.lang3.tuple.Pair;
import org.apache.commons.lang3.tuple.Triple;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

public class CompareData {

    private static final Logger log = LoggerFactory.getLogger(CompareData.class);

    public static final String listTablesSql = "select table_name from information_schema.tables where table_schema = '%s'";

    public static final String listStructuresSql = "select\n" +
                                                   "--\ta.attnum as \"序号\",\n" +
                                                   "--\tc.relname as \"表名\",\n" +
                                                   "--\tcast(obj_description(c.oid) as varchar) as \"表名描述\",\n" +
                                                   "\ta.attname as \"列名\",\n" +
                                                   "\tconcat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\\(.*\\)')) as \"字段类型\"\n" +
                                                   "--\td.description as \"备注\"\n" +
                                                   "from\n" +
                                                   "\tpg_attribute a\n" +
                                                   "left join pg_description d on\n" +
                                                   "\td.objoid = a.attrelid\n" +
                                                   "\tand d.objsubid = a.attnum\n" +
                                                   "left join pg_class c on\n" +
                                                   "\ta.attrelid = c.oid\n" +
                                                   "left join pg_type t on\n" +
                                                   "\ta.atttypid = t.oid\n" +
                                                   "where\n" +
                                                   "\ta.attnum >= 0\n" +
                                                   "\tand c.relname = '%s'\n" +
                                                   "\n" +
                                                   "group by a.attname,t.typname,a.atttypid, a.atttypmod,c.relname,a.attnum\n" +
                                                   "\t\n" +
                                                   "order by\n" +
                                                   "\tc.relname desc,\n" +
                                                   "\ta.attnum asc";

    public static final String countSql = "select count(*) from %s";

    static Connection conn_a;
    static Connection conn_b;
    static String     schema_a;
    static String     schema_b;

    // a conn_a pms3_qkjdy
    static List<Triple<String, Connection, String>> meta;

    static List<String> publicTables;

    static Map<String, List<String>> tableAndFields = new HashMap<>();

    @SuppressWarnings("all")
    public static void main(String[] args) throws Exception {
        String path_prop_a = args[0];
        String path_prop_b = args[1];
        Properties prop_a = new Properties();
        Properties prop_b = new Properties();
        try (FileInputStream in_a = new FileInputStream(path_prop_a);
             FileInputStream in_b = new FileInputStream(path_prop_b)) {
            prop_a.load(in_a);
            prop_b.load(in_b);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        conn_a   = DriverManager.getConnection(prop_a.getProperty("url"), prop_a.getProperty("user"), prop_a.getProperty("password"));
        conn_b   = DriverManager.getConnection(prop_b.getProperty("url"), prop_b.getProperty("user"), prop_b.getProperty("password"));
        schema_a = prop_a.getProperty("schema");
        schema_b = prop_b.getProperty("schema");
        meta     = Lists.newArrayList(Triple.of("a", conn_a, schema_a), Triple.of("b", conn_b, schema_b));

        log.info("正在比对表");
        Map<String, Object> compareTablesMap = compareTableNames();
        publicTables = (List<String>) compareTablesMap.remove("tables");
        compareTablesMap.forEach((k, v) -> {
            log.info(k + ":" + v);
        });

        log.info("正在比对表结构");
        Map<String, Map<String, Object>> compareStructuresMap = compareTableStructures();
        compareStructuresMap.forEach((k, v) -> {
            tableAndFields.put(k, (List<String>) v.remove("fields"));
            log.info("---------------------------------" + k + "---------------------------------");
            v.forEach((k_2, v_2) -> {
                log.info(k_2 + ":" + v_2);
            });
            log.info("---------------------------------" + k + "---------------------------------");
        });

        log.info("比对表数据");

    }

    public static Map<String, Object> compareTableNames() {
        List<Pair<String, Set<String>>> tablesOfAAndB = meta.parallelStream().map(p -> {
            String tableAlias = p.getLeft();
            Connection conn = p.getMiddle();
            String schema = p.getRight();
            Set<String> tables = new HashSet<>();
            try {
                PreparedStatement ps = conn.prepareStatement(String.format(listTablesSql, schema));
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    String tableName = rs.getString(1);
                    tables.add(tableName);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            return Pair.of(tableAlias, tables);
        }).collect(Collectors.toList());

        Set<String> tables_a = tablesOfAAndB.stream().filter(p -> p.getLeft().equals("a")).findAny().get().getRight();
        Set<String> tables_b = tablesOfAAndB.stream().filter(p -> p.getLeft().equals("b")).findAny().get().getRight();
        Map<String, Object> info = new HashMap<>();
        info.put("表数量", Pair.of(tables_a.size(), tables_b.size()));
        //2库表数量有差异
        Set<String> bigTables = tables_a.size() >= tables_b.size() ? tables_a : tables_b;
        List<String> publicTables = new ArrayList<>();
        List<String> onlyTables_a = new ArrayList<>();
        List<String> onlyTables_b = new ArrayList<>();
        for (String table : bigTables) {
            //都有
            if (tables_a.contains(table) && tables_b.contains(table)) {
                publicTables.add(table);
                continue;
            }
            //a有b无
            if (tables_a.contains(table) && !tables_b.contains(table)) {
                onlyTables_a.add(table);
                continue;
            }
            //a无b有
            if (!tables_a.contains(table) && tables_b.contains(table)) {
                onlyTables_b.add(table);
            }
        }
        info.put("tables", publicTables);
        info.put("公共表", publicTables.toString());
        info.put("差异表", Pair.of(onlyTables_a.toString(), onlyTables_b.toString()));

        return info;
    }

    public static Map<String, Map<String, Object>> compareTableStructures() {
        Map<String, Map<String, Object>> map = new HashMap<>();
        for (String table : publicTables) {
            List<Map<String, String>> collect = meta.parallelStream().map(p -> {
                Connection conn = p.getMiddle();
                try {
                    PreparedStatement ps = conn.prepareStatement(String.format(listStructuresSql, table));
                    ResultSet rs = ps.executeQuery();
                    Map<String, String> tempMap = new HashMap<>();
                    tempMap.put(p.getLeft(), null);
                    while (rs.next()) {
                        String field = rs.getString(1);
                        String type = rs.getString(2);
                        tempMap.put(field, type);
                    }
                    return tempMap;
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }).collect(Collectors.toList());

            Map<String, String> fields_a = collect.stream().filter((m -> m.containsKey("a"))).findAny().get();
            fields_a.remove("a");
            Map<String, String> fields_b = collect.stream().filter((m -> m.containsKey("b"))).findAny().get();
            fields_b.remove("b");
            Map<String, String> bigFields = fields_a.size() >= fields_b.size() ? fields_a : fields_b;
            List<String> publicFields = new ArrayList<>();
            List<String> onlyFields_a = new ArrayList<>();
            List<String> onlyFields_b = new ArrayList<>();

            bigFields.forEach((k, v) -> {
                if (fields_a.containsKey(k) && fields_b.containsKey(k)) {
                    publicFields.add(k);
                    return;
                }
                //a有b无
                if (fields_a.containsKey(k) && !fields_b.containsKey(k)) {
                    onlyFields_a.add(k);
                    return;
                }
                //a无b有
                if (!fields_a.containsKey(k) && fields_b.containsKey(k)) {
                    onlyFields_b.add(k);
                }
            });

            Map<String, Object> info = new HashMap<>();
            info.put("字段数量", Pair.of(fields_a.size(), fields_b.size()));
            info.put("fields", publicFields);
            info.put("公共字段", publicFields.toString());
            info.put("差异字段", Pair.of(onlyFields_a.toString(), onlyFields_b.toString()));
            map.put(table, info);
        }
        return map;
    }

//    public static void compareData() {
//        tableAndFields.forEach();
//    }
}
